package com.durablemethods.tsmserver.home;

import java.io.PrintWriter;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.durablemethods.tsmserver.model.TeamSummary;
import com.google.appengine.api.rdbms.AppEngineDriver;



public class SummaryHome {
	private String query;
	public List<TeamSummary> getSummaryList() {
		List<TeamSummary> returnList = new ArrayList<TeamSummary>();
		
		Connection c = null;
	    
		Double totalPayout = 0.0;
	      try {
	    	//printWriter.println("Register Driver - pre");
			DriverManager.registerDriver(new AppEngineDriver());
			//printWriter.println("Register Driver -- done -- getting connections");
			c = DriverManager.getConnection("jdbc:google:rdbms://durablemethods.com:duravest:tsm","jamie","catal0g");
			//printWriter.println("We have a connection");
			Statement s = c.createStatement();
			//printWriter.println("Executing Query -- pre");
			ResultSet rs = s.executeQuery(getQuery());
			while (rs.next()){
				TeamSummary returnSummary = new TeamSummary();
				returnSummary.setTeamName(rs.getString("Team"));
				returnSummary.setTotal(rs.getDouble("Total"));
				returnSummary.setInitialShares(rs.getInt("Quantity"));
				getLastQuery(rs.getInt("TEAM_ID"), returnSummary,c);
				totalPayout = totalPayout + returnSummary.getTotal();
				returnList.add(returnSummary);
			}
			c.close();
		} catch (SQLException e) {
			//printWriter.println("Error found");
			//printWriter.println(e.getMessage());
			e.printStackTrace();
		}
	    for (TeamSummary teamSummary : returnList) {
	    	BigDecimal perShare = new BigDecimal(totalPayout/teamSummary.getInitialShares()).setScale(2,RoundingMode.HALF_DOWN);
	    	teamSummary.setPayout(perShare.doubleValue());
		}
	    
		return returnList;
	}
	public void setQuery(String query) {
		this.query = query;
	}
	public void getLastQuery(Integer teamID, TeamSummary summary, Connection c) throws SQLException{
		StringBuilder builder = new StringBuilder();
		builder.append("select transaction_date, max(price) as price from TEM_SPORTS.TRADES where transaction_date = ("); 
		builder.append(" select max(transaction_date)");
		builder.append(" from TEM_SPORTS.TRADES ");
		builder.append(" where team_id=%s");
		builder.append(" )");
		builder.append(" and team_id=%s");
		String format = String.format(builder.toString(), teamID, teamID);
		PreparedStatement prepareStatement = c.prepareStatement(format);
		ResultSet executeQuery = prepareStatement.executeQuery();
		while (executeQuery.next()){
			SimpleDateFormat sdf = new SimpleDateFormat("MMM/dd");
			summary.setLast(executeQuery.getDouble("price"));
			summary.setLastTrade(sdf.format(executeQuery.getDate("transaction_date")));
		}
		prepareStatement.close();
	}
	public String getQuery() {
		StringBuilder builder = new StringBuilder();
		builder.append("select teams.name as Team, sum(trades.quantity) as Quantity, sum(trades.quantity) * max(teams.price) as Total, TEAM_ID  from"); 
		builder.append(" TEM_SPORTS.TRADES trades, TEM_SPORTS.TEAMS teams");
		builder.append(" where trades.team_id = teams.id");
		builder.append(" and trades.type='I'");
		builder.append(" and trades.trader_id!=1");
		builder.append(" group by teams.id");
		return builder.toString();
	}
	public Map<String, TeamSummary> getTeamsByMap(){
		Map<String, TeamSummary> returnMap = new HashMap<String, TeamSummary>();
		List<TeamSummary> summaryList = getSummaryList();
		for (TeamSummary teamSummary : summaryList) {
			returnMap.put(teamSummary.getTeamName(), teamSummary);
		}
		return returnMap;		
	}
	
	public Double getTotalPayout(){
		Double returnDouble = new Double(0.00);
		List<TeamSummary> summaryList = getSummaryList();
		for (TeamSummary teamSummary : summaryList) {
			returnDouble = returnDouble + teamSummary.getTotal();
		}
		return returnDouble;
	}
	

}
